package com.seek.util;

import com.alibaba.fastjson.JSON;
import java.io.File;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import com.seek.util.file.FileUtil;

import jxl.CellView;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.commons.io.FileUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/***
 * @author lsf
 */
public class ExportExcel {
  private static final Logger logger = LoggerFactory.getLogger(ExportExcel.class);

  /***************************************************************************
   * @param fileName EXCEL文件名称
   * @param listTitle EXCEL文件第一行列标题集合
   * @param listContent EXCEL文件正文数据集合
   * @return
   */
  public final static String exportExcel(String fileName, String sheetName, String[] titles,
      String[] fields, List<Map<String, Object>> listContent) {
    // 以下开始输出到EXCEL
    File file = null;
    String url = "";
    try {
      file = new File(fileName);
      /** **********创建工作簿************ */
      WritableWorkbook workbook = Workbook.createWorkbook(file);

      /** **********创建工作表************ */

      WritableSheet sheet = workbook.createSheet(sheetName, 0);

      /** **********设置纵横打印（默认为纵打）、打印纸***************** */
      jxl.SheetSettings sheetset = sheet.getSettings();
      sheetset.setProtected(false);

      /** ************设置单元格字体************** */
      WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
      WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);

      /** ************以下设置三种单元格样式，灵活备用************ */
      // 用于标题居中
      WritableCellFormat wcf_center = new WritableCellFormat(BoldFont);
      wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
      wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
      wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐
      wcf_center.setWrap(false); // 文字是否换行

      // 用于正文居左
      WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
      wcf_left.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
      wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
      wcf_left.setAlignment(Alignment.LEFT); // 文字水平对齐
      wcf_left.setWrap(false); // 文字是否换行

      CellView cellView = new CellView();
      cellView.setSize(1000 * 10);
      /** ***************以下是EXCEL开头大标题，暂时省略********************* */
      //sheet.mergeCells(0, 0, colWidth, 0);
      //sheet.addCell(new Label(0, 0, "XX报表", wcf_center));
      /** ***************以下是EXCEL第一行列标题********************* */
      for (int i = 0; i < titles.length; i++) {
        sheet.setColumnView(i, cellView);//根据内容自动设置列宽
        sheet.addCell(new Label(i, 0, titles[i], wcf_center));
      }
      /** ***************以下是EXCEL正文数据********************* */

      int i = 1;
      for (Map<String, Object> map : listContent) {
        int j = 0;
        for (String field : fields) {
          Object va = map.get(field);
          if (va == null) {
            va = "";
          }
//          logger.info( "{} type is {}, isnumber {}",va.toString(), va.getClass(), va instanceof java.lang.Number);
          //数字转换为数字格式，方便导出使用excel公式
          if(va instanceof java.lang.Number){
            BigDecimal decimal = new BigDecimal(va.toString());
            sheet.addCell(new Number(j, i, decimal.doubleValue()));
          }else{
            sheet.addCell(new Label(j, i, va.toString(), wcf_left));
          }
          j++;
        }
        i++;
      }
      /** **********将以上缓存中的内容写到EXCEL文件中******** */
      workbook.write();
      /** *********关闭文件************* */
      workbook.close();

      url = UpOtherImgToUpyun.writeFile(file);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        if (null != file) {
          FileUtils.forceDelete(file);
        }
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    return url;
  }

  /***************************************************************************
   * @param fileName EXCEL文件名称
   * @param headText 展示在首行标头
   * @param listTitle EXCEL文件第一行列标题集合
   * @param listContent EXCEL文件正文数据集合
   * @return
   */
  public final static String exportExcel(String fileName, String headText, String[] titles,
      String[] fields, List<Map<String, Object>> listContent, HttpServletResponse response) {
    String result = "系统提示：Excel文件导出成功！";
    // 以下开始输出到EXCEL
    try {
      //定义输出流，以便打开保存对话框______________________begin
      OutputStream os = response.getOutputStream();// 取得输出流
      response.reset();// 清空输出流
      response.setHeader("Content-disposition",
          "attachment; filename=" + new String(fileName.getBytes("GB2312"), "ISO8859-1"));
// 设定输出文件头      
      response.setContentType("application/msexcel");// 定义输出类型
      //定义输出流，以便打开保存对话框_______________________end

      /** **********创建工作簿************ */
      WritableWorkbook workbook = Workbook.createWorkbook(os);

      /** **********创建工作表************ */

      WritableSheet sheet = workbook.createSheet("Sheet1", 0);

      /** **********设置纵横打印（默认为纵打）、打印纸***************** */
      jxl.SheetSettings sheetset = sheet.getSettings();
      sheetset.setProtected(false);

      /** ************设置单元格字体************** */
      WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
      WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);

      /** ************以下设置三种单元格样式，灵活备用************ */
      // 用于标题居中
      WritableCellFormat wcf_center = new WritableCellFormat(BoldFont);
      wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
      wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
      wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐
      wcf_center.setWrap(false); // 文字是否换行

      // 用于正文居左
      WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
      wcf_left.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
      wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
      wcf_left.setAlignment(Alignment.LEFT); // 文字水平对齐
      wcf_left.setWrap(false); // 文字是否换行

      CellView cellView = new CellView();
      cellView.setSize(1000 * 10);
      /** ***************以下是EXCEL开头大标题，暂时省略********************* */
      //sheet.mergeCells(0, 0, colWidth, 0);
      //sheet.addCell(new Label(0, 0, "XX报表", wcf_center));
      /** ***************以下是EXCEL第一行列标题********************* */
      sheet.setColumnView(0, cellView);//根据内容自动设置列宽
      sheet.mergeCells(0, 0, titles.length - 1, 0);
      sheet.addCell(new Label(0, 0, headText, wcf_center));
      for (int i = 0; i < titles.length; i++) {
        sheet.setColumnView(i, cellView);//根据内容自动设置列宽
        sheet.addCell(new Label(i, 1, titles[i], wcf_center));
      }
      /** ***************以下是EXCEL正文数据********************* */
      int i = 2;
      for (Map<String, Object> map : listContent) {
        int j = 0;
        for (String field : fields) {
          Object va = map.get(field);
          if (va == null) {
            va = "";
          }
          sheet.addCell(new Label(j, i, va.toString(), wcf_left));
          j++;
        }
        i++;
      }
      /** **********将以上缓存中的内容写到EXCEL文件中******** */
      workbook.write();
      /** *********关闭文件************* */
      workbook.close();

    } catch (Exception e) {
      result = "系统提示：Excel文件导出失败，原因：" + e.toString();
      System.out.println(result);
      e.printStackTrace();
    }
    return result;
  }


  /***************************************************************************
   * @param fileName EXCEL文件名称
   * @param listTitle EXCEL文件第一行列标题集合
   * @param listContent EXCEL文件正文数据集合
   * @return
   */
  public final static String exportTaskExcelSave(String fileName, String[] titles, String[] fields,
      List<Map<String, Object>> listContent) {
    String result = "";
    // 以下开始输出到EXCEL
    try {

      String uploadDir = "upload/task";
      uploadDir = uploadDir + "/" + new SimpleDateFormat("yyyyMMdd").format(new Date());
      String destDir = FileUtil._getDestDir(uploadDir); //获得目标目录
      File destFile = new File(destDir + "/" + fileName); //上传文件全路径
      result += uploadDir + "/" + fileName;
      //result += destDir + "/" + fileName;

      WritableWorkbook workbook = Workbook.createWorkbook(destFile);

      WritableSheet sheet = workbook.createSheet("任务", 0);

      /** **********设置纵横打印（默认为纵打）、打印纸***************** */
      jxl.SheetSettings sheetset = sheet.getSettings();
      sheetset.setProtected(false);

      /** ************设置单元格字体************** */
      WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
      WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);

      /** ************以下设置三种单元格样式，灵活备用************ */
      // 用于标题居中
      WritableCellFormat wcf_center = new WritableCellFormat(BoldFont);
      wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
      wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
      wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐
      wcf_center.setWrap(false); // 文字是否换行

      // 用于正文居左
      WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
      wcf_left.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
      wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
      wcf_left.setAlignment(Alignment.LEFT); // 文字水平对齐
      wcf_left.setWrap(false); // 文字是否换行

      CellView cellView = new CellView();
      cellView.setSize(1000 * 10);
      /** ***************以下是EXCEL开头大标题，暂时省略********************* */
      //sheet.mergeCells(0, 0, colWidth, 0);
      //sheet.addCell(new Label(0, 0, "XX报表", wcf_center));
      /** ***************以下是EXCEL第一行列标题********************* */
      for (int i = 0; i < titles.length; i++) {
        sheet.setColumnView(i, cellView);//根据内容自动设置列宽
        sheet.addCell(new Label(i, 0, titles[i], wcf_center));
      }
      /** ***************以下是EXCEL正文数据********************* */
      int i = 1;
      for (Map<String, Object> map : listContent) {
        int j = 0;
        for (String field : fields) {
          Object va = map.get(field);
          if (va == null) {
            va = "";
          }
          sheet.addCell(new Label(j, i, va.toString(), wcf_left));
          j++;
        }
        i++;
      }
      /** **********将以上缓存中的内容写到EXCEL文件中******** */
      workbook.write();
      /** *********关闭文件************* */
      workbook.close();

    } catch (Exception e) {
      result = "系统提示：Excel文件导出失败，原因：" + e.toString();
      System.out.println(result);
      e.printStackTrace();
    }
    return result;
  }
}
